﻿-- ===============================================================================================
-- Basic View for Menus
-- ===============================================================================================

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VUserMenus]'))
	DROP VIEW [esf_sso].[VUserMenus]
GO

-- SELECT * FROM [esf_sso].[VUserMenus]

CREATE VIEW [esf_sso].[VUserMenus] AS
	
	WITH TreeBasRef (
		RelID, ChiBR_ID, ParBR_ID, ParBR_BasicReferenceTypeID, Level, ChiBR_BasicReferenceTypeID
		,ChiBR_Code, ParBR_Code, ChiBR_Name, ChiBR_Description, ChiBR_Extra_I, ChiBR_Extra_II, ChiBR_Extra_III, ChiBR_IsEnabled 
		,ChiBR_CreationDateTime, ChiBR_LastUpdateDateTime, Application_ID, Application_Code, Application_Name, Application_IsEnabled, URL)
	AS
	(
		-- First Level
		SELECT 
			TBR.ID
			,VBR.ID
			,NULL
			,NULL
			,0
			,VBR.BasicReferenceTypeID
			,VBR.Code
			,CONVERT (VARCHAR (100), NULL)
			,VBR.Name
			,VBR.Description
			,VBR.Extra_I
			,VBR.Extra_II
			,VBR.Extra_III
			,VBR.IsEnabled
			,VBR.CreationDateTime
			,VBR.LastUpdateDateTime
			,VBR.Application_ID
			,VBR.Application_Code
			,VBR.Application_Name
			,VBR.Application_IsEnabled
			,VBR.URL
		FROM 
			esf_sso.TreeBasicReference TBR
		INNER JOIN
			esf_sso.VMenu VBR ON 
				VBR.ID = TBR.ParentBasicReferenceID
				AND VBR.BasicReferenceTypeID = 7
		WHERE 
			TBR.BasicReferenceID IS NULL
		
		UNION ALL
		-- Recursive member definition
		SELECT 
			TBR.ID
			,VBR.ID
			,TBRF.ChiBR_ID
			,TBRF.ChiBR_BasicReferenceTypeID
			,TBRF.Level + 1
			,VBR.BasicReferenceTypeID
			,VBR.Code
			,CONVERT (VARCHAR (100), TBRF.ChiBR_Code)
			,VBR.Name
			,VBR.Description
			,VBR.Extra_I
			,VBR.Extra_II
			,VBR.Extra_III
			,VBR.IsEnabled
			,VBR.CreationDateTime
			,VBR.LastUpdateDateTime
			,VBR.Application_ID
			,VBR.Application_Code
			,VBR.Application_Name
			,VBR.Application_IsEnabled
			,VBR.URL
		FROM 
			esf_sso.TreeBasicReference TBR
		INNER JOIN
			esf_sso.VMenu VBR ON 
				VBR.ID = TBR.BasicReferenceID
				AND VBR.BasicReferenceTypeID = 7
		INNER JOIN
			TreeBasRef TBRF ON TBRF.ChiBR_ID = TBR.ParentBasicReferenceID
	)

	SELECT
		*
	FROM 
		TreeBasRef
	
GO